﻿Imports System.Data.SqlClient
Imports System.Linq
Public Class frmEditClasses
    Dim classID As Integer
    Dim origClassTime As Char
    Dim courseID As String
    Dim CN As New SqlConnection
    Dim availability As Dictionary(Of Char, String)
    Public Sub New(ByVal courseID As String, ByVal ClassTime As Char)
        Me.InitializeComponent()
        Me.courseID = courseID
        origClassTime = ClassTime
    End Sub
    Private Sub frmEditClasses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN.ConnectionString = My.Settings.IT308ConnectionString
        availability = New Dictionary(Of Char, String)
        availability.Add(CChar("a"), "Monday Morning")
        availability.Add(CChar("b"), "Monday Afternoon")
        availability.Add(CChar("c"), "Monday Evening")
        availability.Add(CChar("d"), "Tuesday Morning")
        availability.Add(CChar("e"), "Tuesday Afternoon")
        availability.Add(CChar("f"), "Tuesday Evening")
        availability.Add(CChar("g"), "Wednesday Morning")
        availability.Add(CChar("h"), "Wednesday Afternoon")
        availability.Add(CChar("i"), "Wednesday Evening")
        availability.Add(CChar("j"), "Thursday Morning")
        availability.Add(CChar("k"), "Thursday Afternoon")
        availability.Add(CChar("l"), "Thursday Evening")
        availability.Add(CChar("m"), "Friday Morning")
        availability.Add(CChar("n"), "Friday Afternoon")
        availability.Add(CChar("o"), "Friday Evening")

        'WB - creates the connection to database
        Dim LI As New ListViewItem
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "Select CLASSES.ID, Teacher.FName, Teacher.LName FROM CLASSES inner join TEACHER on CLASSES.TeacherID = TEACHER.ID WHERE ClassTime = @ClassTime AND CoursesID = @CourseID"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", courseID)
        cmd.Parameters.AddWithValue("@ClassTime", origClassTime)

        'WB - tries to open the connection, reports an error it it fails
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Could not connect to the Database - " & ex.Message, "Error")
        End Try

        'WB - calls the procedure to generate the possibilites and places the courses in lsvCourses
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                classID = Convert.ToInt32(Reader.Item("ID"))
                cboTeacher.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
            Reader.Close()
        Catch ex As Exception
            MessageBox.Show("Cannot Load Currently Scheduled Classes - " & ex.Message, "Error")
        Finally
            Try
                Reader.Close()
            Catch ex As Exception
            End Try
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
        load_lstStudents()
        load_cboTeacher()
        load_lstAllStudents()
        load_cboClassTime()
        Me.Text = Me.Text & " - " & courseID
        lblStudents.Text = courseID & " " & lblStudents.Text
    End Sub
    Private Sub load_lstStudents()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT STUDENTS.FName, STUDENTS.LName From STUDENTSCLASSES inner join CLASSES on STUDENTSCLASSES.ClassesID = CLASSES.ID inner join STUDENTS on STUDENTSCLASSES.StudentsID = STUDENTS.ID WHERE CLASSES.CoursesID = @CourseID AND CLASSES.ClassTime = @ClassTime"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", courseID)
        cmd.Parameters.AddWithValue("@ClassTime", origClassTime)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        lstStudents.Items.Clear()

        'WB - executes the query and loads the Students names into lstStudents
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                lstStudents.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot load Students - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try
    End Sub
    Private Sub load_cboTeacher()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT  DISTINCT TEACHER.FName, TEACHER.LName, QUALIFICATIONS.RATING FROM QUALIFICATIONS inner join CLASSES on QUALIFICATIONS.CoursesID = CLASSES.CoursesID inner join TEACHER on QUALIFICATIONS.TeacherID = TEACHER.ID WHERE CLASSES.CoursesID = @CourseID AND TEACHER.ID not in(SELECT TeacherID FROM CLASSES WHERE ClassTime = @ClassTime) Order by QUALIFICATIONS.RATING"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", courseID)
        cmd.Parameters.AddWithValue("@ClassTime", origClassTime)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - executes the query and loads the Students names into lstStudents
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                cboTeacher.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
                cboTeacher.SelectedIndex = 0
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot load Teachers - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try

    End Sub
    Private Sub load_lstAllStudents()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT STUDENTS.FName, STUDENTS.LName From STUDENTSCLASSES inner join CLASSES on STUDENTSCLASSES.ClassesID = CLASSES.ID inner join STUDENTS on STUDENTSCLASSES.StudentsID = STUDENTS.ID WHERE CLASSES.CoursesID <> @CourseID AND CLASSES.ClassTime <> @ClassTime"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", courseID)
        cmd.Parameters.AddWithValue("@ClassTime", origClassTime)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        lstAllStudents.Items.Clear()

        'WB - executes the query and loads the Students names into lstStudents
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                lstAllStudents.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot load Students - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If lstAllStudents.SelectedIndex <> -1 Then
            'WB - creates the connection to the database. and loads the paramaters for the query
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            cmd.CommandText = "INSERT INTO STUDENTSCLASSES (ClassesID, StudentsID) SELECT @ClassesID AS ClassesID, ID AS StudentID FROM STUDENTS WHERE FName = @FName AND LName = @LName"
            cmd.Parameters.AddWithValue("@ClassesID", classID)
            cmd.Parameters.AddWithValue("@FName", lstAllStudents.SelectedItem.ToString.Substring(0, lstAllStudents.SelectedItem.ToString.IndexOf(" ")).Trim)
            cmd.Parameters.AddWithValue("@LName", lstAllStudents.SelectedItem.ToString.Substring(lstAllStudents.SelectedItem.ToString.IndexOf(" ")).Trim)
            'WB - tries to open the connection and reports the error if it fails.
            Try
                CN.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
            End Try

            'WB - executes the query and loads the Students names into lstStudents
            Try
                If cmd.ExecuteNonQuery = 0 Then
                    MessageBox.Show("Cannot Add Student", "Error")
                End If
            Catch ex As Exception
                MessageBox.Show("Cannot Add Student - " & ex.Message, "Error")
            Finally
                If CN.State = ConnectionState.Open Then CN.Close()
            End Try
            load_lstAllStudents()
            load_lstStudents()
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If lstStudents.SelectedIndex <> -1 Then
            'WB - creates the connection to the database. and loads the paramaters for the query
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            cmd.CommandText = "DELETE STUDENTSCLASSES WHERE ClassesID = @ClassesID and StudentsID in (SELECT ID FROM STUDENTS WHERE FName = @FName AND LName = @LName)"
            cmd.Parameters.AddWithValue("@ClassesID", classID)
            cmd.Parameters.AddWithValue("@FName", lstStudents.SelectedItem.ToString.Substring(0, lstStudents.SelectedItem.ToString.IndexOf(" ")).Trim)
            cmd.Parameters.AddWithValue("@LName", lstStudents.SelectedItem.ToString.Substring(lstStudents.SelectedItem.ToString.IndexOf(" ")).Trim)
            'WB - tries to open the connection and reports the error if it fails.
            Try
                CN.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
            End Try

            'WB - executes the query and loads the Students names into lstStudents
            Try
                If cmd.ExecuteNonQuery = 0 Then
                    MessageBox.Show("Cannot Remove Student", "Error")
                End If
            Catch ex As Exception
                MessageBox.Show("Cannot Remove Student - " & ex.Message, "Error")
            Finally
                If CN.State = ConnectionState.Open Then CN.Close()
            End Try
            load_lstStudents()
            load_lstAllStudents()
        End If
    End Sub
    Private Sub load_cboClassTime()
        For Each DictionaryEntry In availability
            cboClassTime.Items.Add(DictionaryEntry.Value)
        Next
        cboClassTime.SelectedItem = availability(origClassTime)
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        Dim foo = From entry In availability Where entry.Value = cboClassTime.SelectedItem.ToString Select entry.Key
        cmd.Connection = CN
        cmd.CommandText = "UPDATE CLASSES SET TeacherID = (SELECT ID FROM TEACHER WHERE FName = @FName AND LName = @LName), ClassTime = @ClassTime WHERE ID = @ClassesID"
        cmd.Parameters.AddWithValue("@ClassesID", classID)
        cmd.Parameters.AddWithValue("@FName", cboTeacher.SelectedItem.ToString.Substring(0, cboTeacher.SelectedItem.ToString.IndexOf(" ")).Trim)
        cmd.Parameters.AddWithValue("@LName", cboTeacher.SelectedItem.ToString.Substring(cboTeacher.SelectedItem.ToString.IndexOf(" ")).Trim)
        cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - executes the query and loads the Students names into lstStudents
        Try
            If cmd.ExecuteNonQuery = 0 Then
                MessageBox.Show("Cannot Save Class", "Error")
            End If
        Catch ex As Exception
            MessageBox.Show("Cannot Save Class - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
End Class